const connection = require('../../sql');
const SimpleDataScopeHelper = require('../../utils/SimpleDataScopeHelper');

class StatisticsService {
	async createStatistics({ year, month, teacherId, teacherCommonType, totalTeaching, totalClassCount, totalHours, remark, deptScope, createId }) {
		const conn = await connection.getConnection();
		try {
			await conn.beginTransaction();

			const sql = `
        INSERT INTO teacher_statistics (
          year, month, during_time, teacher_id, teacher_common_type, total_teaching, total_class_count, total_hours,
          create_time, update_time, create_id, update_id, dept_scope, remark
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW(), ?, ?, ?, ?)
      `;
			const duringTime = `${year}-${month.padStart(2, '0')}`;
			const deptScopeJson = JSON.stringify({ ids: deptScope || [] });

			const [result] = await conn.execute(sql, [
				year,
				month,
				duringTime,
				teacherId,
				teacherCommonType,
				totalTeaching || null,
				totalClassCount || null,
				totalHours || null,
				createId,
				createId,
				deptScopeJson,
				remark || null,
			]);

			await conn.commit();
			return { id: result.insertId };
		} catch (error) {
			await conn.rollback();
			throw error;
		} finally {
			conn.release();
		}
	}

	async deleteStatistics(ctx, ids) {
		const conn = await connection.getConnection();
		try {
			await conn.beginTransaction();

			SimpleDataScopeHelper.start('teacher_statistics');
			const { whereClause, params } = await SimpleDataScopeHelper.applyScope(ctx.state.user.id, 'teacher_statistics');

			const sql = `
        DELETE FROM teacher_statistics 
        WHERE id IN (${ids.map(() => '?').join(',')})
        ${whereClause}
      `;
			const [result] = await conn.execute(sql, [...ids, ...params]);

			if (result.affectedRows === 0) {
				throw new Error('记录不存在或无权限删除');
			}

			await conn.commit();
			return { deletedCount: result.affectedRows };
		} catch (error) {
			await conn.rollback();
			throw error;
		} finally {
			conn.release();
			SimpleDataScopeHelper.clearDataScope();
		}
	}

	async updateStatistics(ctx, { id, year, month, teacherId, teacherCommonType, totalTeaching, totalClassCount, totalHours, remark, deptScope }) {
		const conn = await connection.getConnection();
		try {
			await conn.beginTransaction();

			SimpleDataScopeHelper.start('teacher_statistics');
			const { whereClause, params } = await SimpleDataScopeHelper.applyScope(ctx.state.user.id, 'teacher_statistics');

			const sql = `
        UPDATE teacher_statistics 
        SET 
          year = ?, month = ?, during_time = ?, teacher_id = ?, teacher_common_type = ?, 
          total_teaching = ?, total_class_count = ?, total_hours = ?, remark = ?, 
          dept_scope = ?, update_id = ?, update_time = NOW()
        WHERE id = ?
        ${whereClause}
      `;
			const duringTime = `${year}-${month.padStart(2, '0')}`;
			const deptScopeJson = JSON.stringify({ ids: deptScope || [] });

			const [result] = await conn.execute(sql, [
				year,
				month,
				duringTime,
				teacherId,
				teacherCommonType,
				totalTeaching || null,
				totalClassCount || null,
				totalHours || null,
				remark || null,
				deptScopeJson,
				ctx.state.user.id,
				id,
				...params,
			]);

			if (result.affectedRows === 0) {
				throw new Error('记录不存在或无权限修改');
			}

			await conn.commit();
			return { id };
		} catch (error) {
			await conn.rollback();
			throw error;
		} finally {
			conn.release();
			SimpleDataScopeHelper.clearDataScope();
		}
	}

	async listStatistics(userId, { page = 1, limit = 10, year, month }) {
		const conn = await connection.getConnection();
		try {
			SimpleDataScopeHelper.start('teacher_statistics');
			const { whereClause, params } = await SimpleDataScopeHelper.applyScope(userId, 'teacher_statistics');

			const offset = (page - 1) * limit;
			let conditionClause = '';
			const conditionParams = [];

			if (year) {
				conditionClause += ' AND year = ?';
				conditionParams.push(year);
			}
			if (month) {
				conditionClause += ' AND month = ?';
				conditionParams.push(month);
			}

			const countSql = `
			SELECT COUNT(*) as total 
			FROM teacher_statistics 
			${whereClause} ${conditionClause}
		  `;
			const listSql = `
			SELECT 
			  id, year, month, during_time AS duringTime, teacher_id AS teacherId, 
			  teacher_common_type AS teacherCommonType, total_teaching AS totalTeaching, 
			  total_class_count AS totalClassCount, total_hours AS totalHours, 
			  check_status AS checkStatus, check_time AS checkTime, 
			  create_time AS createTime, update_time AS updateTime, 
			  last_sync_time AS lastSyncTime, remark, create_id AS createId, 
			  update_id AS updateId, dept_scope AS deptScope
			FROM teacher_statistics 
			${whereClause} ${conditionClause}
			ORDER BY create_time DESC
			LIMIT ? OFFSET ?
		  `;
			const [countResult] = await conn.execute(countSql, [...params, ...conditionParams]);
			const [rows] = await conn.execute(listSql, [...params, ...conditionParams, String(limit), String(offset)]);

			return {
				current: page,
				limit,
				totalPage: Math.ceil(countResult[0].total / limit),
				total: countResult[0].total,
				rows,
			};
		} finally {
			conn.release();
			SimpleDataScopeHelper.clearDataScope();
		}
	}
}

module.exports = new StatisticsService();
